﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SMTUploadProductionData.model
{
    class ReadWriteDatabase
    {
        SqlConnection connection;
        string connectionString = "Data Source=192.168.5.97;Initial Catalog=SMTMachineProduce;User ID=sa;Password=gl.2008";
        public ReadWriteDatabase(ref string exception)
        {
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();
            }
            catch (Exception ex)
            {
                exception = ex.ToString();
                return;
            }
        }

        public async Task InsertDatabase(object obj, string tableName)
        {
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties();
            List<string> columnNames = new List<string>();
            List<string> parameterNames = new List<string>();
            List<string> value = new List<string>();
            foreach (PropertyInfo property in properties)
            {
                if (property.Name == "id") continue;
                string columnName = property.Name;
                string parameterName = $"@{columnName}";
                columnNames.Add(columnName);
                parameterNames.Add(parameterName);
                value.Add(property.GetValue(obj).ToString());
            }
            string columns = string.Join(", ", columnNames);
            string parameters = string.Join(", ", parameterNames);
            string insertQuery = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand sqlcd = new SqlCommand(insertQuery, sqlConnection))
                {
                    for (int i = 0; i < parameterNames.Count; i++)
                    {
                        sqlcd.Parameters.AddWithValue(parameterNames[i], value[i]);
                    }
                    sqlcd.ExecuteNonQuery();
                }
            }
        }


        public async Task<string> QueryData(string tabName, string where1, string FName, string where2, string MNane)
        {
            try
            {
                string str_Selects = $"select * from {tabName} where {where1} ='{FName}'and {where2} ='{MNane}'";
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(str_Selects, sqlConnection))
                    {
                        using (SqlDataReader sr = command.ExecuteReader())
                        {
                            if (sr.HasRows)
                            {
                                while (sr.Read())
                                {
                                    return sr[where1].ToString();
                                }
                            }
                        }
                    }
                }
                return "";
            }
            catch (Exception ){return "1";}
        }

        public async Task<bool> QueryData(string Query)
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(Query, sqlConnection))
                    {
                        using (SqlDataReader sr = command.ExecuteReader())
                        {
                            if (sr.HasRows)
                            {
                                while (sr.Read())
                                {
                                    return false;
                                }
                            }
                        }
                    }
                }
                return true;
            }
            catch (Exception) { return true; }
        }


        public async Task<int> RowNumber(string TabName,string MachineNameValue, string colName, string content)
        {
            string str_Select = $"SELECT COUNT(*) FROM {TabName} WHERE MachineName = '{MachineNameValue}' and CAST({colName} AS DATE)=CAST('{DateTime.Now.Date}' AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    command.Parameters.AddWithValue("@MachineNameValue", content);
                    object result = await command.ExecuteScalarAsync();
                    return result != null ? Convert.ToInt32(result) : 0;
                }
            }
        }

        public async Task<int> InsertJukiData(juki_model juki)
        {
            try
            {
                string sqlSelects = $"select * from juki_Producedate where MachineName ='{juki.MachineName}' AND model ='{juki.model}' AND CAST(Time AS DATE) =CAST('{DateTime.Now.Date}' AS DATE)";
                using (SqlCommand command = new SqlCommand(sqlSelects, connection))
                {
                    using (SqlDataReader sr = command.ExecuteReader())
                    {
                        if (sr.HasRows)
                        {
                            while (sr.Read())
                            {
                                selectjukiUpdate(juki);
                                return 0;
                            }
                        }
                    }
                }

                await InsertDatabase(juki, "juki_Producedate");
                return 1;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public void selectjukiUpdate(juki_model juki)
        {
            try
            {
                Type type = juki.GetType();
                PropertyInfo[] properties = type.GetProperties();
                List<string> columnNames = new List<string>();
                List<string> value = new List<string>();
                foreach (PropertyInfo property in properties)
                {
                    string columnName = property.Name;
                    string str_select = $"select * from juki_Producedate where MachineName='{juki.MachineName}' and  model='{juki.model}' and CAST(Time as date)=CAST(GETDATE() as date)";
                    string str_update = "";
                    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                    {
                        sqlConnection.Open();
                        using (SqlCommand command = new SqlCommand(str_select, sqlConnection))
                        {
                            using (SqlDataReader sr = command.ExecuteReader())
                            {
                                if (sr.HasRows)
                                {
                                    while (sr.Read())
                                    {
                                        str_update = $"update juki_Producedate set {columnName}='{property.GetValue(juki)}' where id='{sr["id"]}'";
                                    }
                                }
                            }
                        }
                    }
                    
                    if (str_update != "")
                    {
                        using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                        {
                            sqlConnect.Open();
                            using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                            {
                                sql.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MainForm.ErrorRecord(ex.ToString() + DateTime.Now);
            }
        }


        public async Task<List<T>> QueryDataAsync<T>(string selectString) where T : new()
        {
            var result = new List<T>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (SqlCommand command = new SqlCommand(selectString, connection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        var properties = typeof(T).GetProperties().ToDictionary(p => p.Name, StringComparer.OrdinalIgnoreCase);
                        while (dr.Read())
                        {
                            var item = new T();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                var columnName = dr.GetName(i);
                                if (properties.TryGetValue(columnName, out var property) && !dr.IsDBNull(i))
                                {
                                    var value = dr.GetValue(i);
                                    var convertedValue = Convert.ChangeType(value, property.PropertyType);
                                    property.SetValue(item, convertedValue);
                                }
                            }
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }

        public async Task<List<string>> SelectDataAsync(string TabName, string colName)
        {
            List<string> list = new List<string>();
            string str_Select = $"SELECT DISTINCT MachineName FROM {TabName} where CAST({colName} AS DATE)=CAST(GETDATE() AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    SqlDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        list.Add(dr["MachineName"].ToString());
                    }
                }
            }
            return list;
        }

        public async Task<int> SelectDataAsyncCategory(string TabName, string colName)
        {
            try
            {
                List<string> list = new List<string>();
                string str_Select = $"SELECT DISTINCT MachineName FROM {TabName} where CAST({colName} AS DATE)=CAST(GETDATE() AS DATE)";
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                    {
                        SqlDataReader dr = command.ExecuteReader();
                        while (dr.Read())
                        {
                            list.Add(dr["MachineName"].ToString());
                        }
                    }
                }
                return list.Count;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public async Task<bool> SelectData(string query)
        {
            using (SqlConnection sqlConnect = new SqlConnection(connectionString))
            {
                await sqlConnect.OpenAsync();
                using (SqlCommand command = new SqlCommand(query, sqlConnect))
                {
                    using (SqlDataReader sr = await command.ExecuteReaderAsync())
                    {
                        if (sr.HasRows)
                        {
                            while (sr.Read())
                            {
                                return true;
                            }
                        }
                    }
                }
            }
            return false;
        }

        public async Task UpdateDate(object obj, string query, string table)
        {
            try
            {
                Type type = obj.GetType();
                PropertyInfo[] properties = type.GetProperties();
                List<string> columnNames = new List<string>();
                List<string> value = new List<string>();
                foreach (PropertyInfo property in properties)
                {
                    string columnName = property.Name;
                    string str_update = "";
                    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                    {
                        await sqlConnection.OpenAsync();
                        using (SqlCommand command = new SqlCommand(query, sqlConnection))
                        {
                            using (SqlDataReader sr = await command.ExecuteReaderAsync())
                            {
                                if (sr.HasRows)
                                {
                                    while (sr.Read())
                                    {
                                        str_update = $"update {table} set {columnName}='{property.GetValue(obj)}' where id='{sr["id"]}'";
                                    }
                                }
                            }
                        }
                    }
                    if (str_update != "")
                    {
                        using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                        {
                            await sqlConnect.OpenAsync();
                            using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                            {
                                await sql.ExecuteNonQueryAsync();
                            }
                        }
                    }
                }
            }
            catch (Exception )
            {
                return;
            }



        }
    }
}
